
* Data file at: https://www.federalreserve.gov/releases/efa/fwtw.htm

*Note: Tables generated off of included CSV file that had been made available at the time of writing of first draft, in early 2023. Updated files are likely to generate slightly different numbers, due to possible underlying data revisions.

import delimited "fwtw_data (1).csv"



gen dateq=quarterly(date, "YQ")
format dateq %tq

* Lack of consistent data for CEF and ETF 

drop if issuercode==71|holdercode==71
drop if issuercode==89|holdercode==89

* Drop Mon Authority and Grand Total Rows

drop if issuercode==55|holdercode==55
drop if issuercode==56|holdercode==56

*Toggle if want to generate data for just one quarter (eg, matrix tables) 

keep if date=="2023Q1"

* Gen a consistent "Banks" aggregate

egen banks=sum(level) if issuername=="US-Chartered"|issuername=="Credit Unions" |issuername=="Banks in U.S.-Affiliated Areas" |issuername=="FBOs" |issuername=="Holding Companies", by (instrumentname holdername date)
replace issuercode=70 if issuername=="US-Chartered"|issuername=="Credit Unions" |issuername=="Banks in U.S.-Affiliated Areas" |issuername=="FBOs" |issuername=="Holding Companies"
egen tag=tag(banks)
replace issuername = "Banks" if tag==1
drop if tag==0&banks~=.
replace level=banks if tag==1


drop tag banks

egen banks=sum(level) if holdername=="US-Chartered"|holdername=="Credit Unions" |holdername=="Banks in U.S.-Affiliated Areas" |holdername=="FBOs" |holdername=="Holding Companies", by (instrumentname issuername date)
replace holdercode=70 if holdername=="US-Chartered"|holdername=="Credit Unions" |holdername=="Banks in U.S.-Affiliated Areas" |holdername=="FBOs" |holdername=="Holding Companies"
egen tag=tag(banks)
sort instrumentname issuercode holdercode
replace holdername = "Banks" if tag==1
drop if tag==0&banks~=.
replace level=banks if tag==1


drop tag banks

* Gen a consistent "Government" aggregate to be included in "Real Sector"

egen gov=sum(level) if holdername=="Federal Govt."|holdername=="State/Local Govt." , by (instrumentname issuername date)
replace holdercode=30 if holdername=="Federal Govt."|holdername=="State/Local Govt." 
egen tag=tag(gov)
replace holdername = "Gov" if tag==1
drop if tag==0&gov~=.
replace level=gov if tag==1


drop tag gov




egen gov=sum(level) if issuername=="Federal Govt."|issuername=="State/Local Govt." , by (instrumentname holdername date)
replace issuercode=30 if issuername=="Federal Govt."|issuername=="State/Local Govt." 
egen tag=tag(gov)
replace issuername = "Gov" if tag==1
drop if tag==0&gov~=.
replace level=gov if tag==1


drop tag gov


* Gen a consistent "Real" sector

egen real=sum(level) if issuercode==14|issuercode==15|issuercode==30, by (instrumentname holdername date) 
replace issuercode=10 if issuercode==14|issuercode==15|issuercode==30
egen tag=tag(real)
replace issuername = "Real" if tag==1
drop if tag==0&real~=.
replace level=real if tag==1

drop tag real

egen real=sum(level) if holdercode==14|holdercode==15|holdercode==30, by (instrumentname issuername date) 
replace holdercode=10 if holdercode==14|holdercode==15|holdercode==30
egen tag=tag(real)
replace holdername = "Real" if tag==1
drop if tag==0&real~=.
replace level=real if tag==1

drop tag real



 
*** Issuer by holder matrix 

collapse (sum) level, by (issuername holdername date)
order date issuername holdername

****


egen totissuer=sum(level), by(issuername date)
format totissuer  %12.0fc

egen totholder=sum(level), by(holdername date)
format totholder  %12.0fc


egen grandtotal=sum(level), by(date)
format grandtotal  %12.0fc


sort issuername holdername date

gen share_issuer=level/totissuer
gen share_holder=level/totholder

order date issuername holdername share_issuer share_holder

